﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using Pub.Tools;
using Pub;

namespace Pub.Lib
{
    public static class Helper
    {
        private static void SetWaitCursor()
        {
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
        }

        private static void SetDefaultCursor()
        {
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
        }



        public static class DBHelper
        {


            /// <summary>
            /// 首次连接,读取参数
            /// </summary>
            public static void HelperTryFirstConnet()
            {

                try
                {
                    string _UpdateSQL = XmlTools.Read(PubVar.SysSetXml, "SysSet/UpdateSQL");
                    try
                    {
                        PubVar.SysUpdateSQL = SysTools.ToBoolean(_UpdateSQL);
                    }
                    catch
                    {
                    }
                    PubVar.SysSecure_Domain = DESEncrypt.Decrypt(XmlTools.Read(PubVar.SysSetXml, "SysSet/Remoting/Domain"));
                    PubVar.SysSecure_Username = DESEncrypt.Decrypt(XmlTools.Read(PubVar.SysSetXml, "SysSet/Remoting/Username"));
                    PubVar.SysSecure_Password = DESEncrypt.Decrypt(XmlTools.Read(PubVar.SysSetXml, "SysSet/Remoting/Password"));

                }
                catch (Exception ex)
                {
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【语句错误】HelperTryFirstConnet提示:{0}.\r\n", ex.Message.ToString()));
                }
                HelperChooiceServer();
            }

            /// <summary>
            /// 随机选择服务器并尝试连接
            /// </summary>
            public static void HelperChooiceServer()
            {

                try
                {
                    SetWaitCursor();
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                Remoting.TryFirstConnet();
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.TryFirstConnet();
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                WCF.LoadWCF();
                                break;
                            }
                    }

                }
                catch (Exception ex)
                {
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【语句错误】HelperChooiceServer提示:{0}.\r\n", ex.Message.ToString()));
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    System.Environment.Exit(System.Environment.ExitCode);
                }

                finally
                {
                    SetDefaultCursor();
                }

            }

            /// <summary>
            /// 获取连接字符串的服务器时间
            /// </summary>
            /// <param name="_conn">连接名称</param>
            /// <returns>返回连接字符串的服务器时间</returns>
            public static DateTime GetServerTime(string _conn)
            {
                DateTime datetime = DateTime.Now;
                string strMess = string.Empty;
                switch (PubVar.SysDAT)
                {
                    case DataAccessType.CS:
                        {
                            DBAccess dbAccess = new DBAccess();
                            datetime = dbAccess.GetServerTime(_conn,ref  strMess);
                            break;
                        }
                    case DataAccessType.REMOTING:
                        {
                            datetime = PubVar.objdb.GetServerTime(_conn,ref  strMess);
                            break;
                        }

                    case DataAccessType.WCF:
                        {
                            datetime = PubVar.WcfProxy.IGetServerTime(PubVar.SysArrAuth, _conn, ref strMess);
                            break;
                        }
                }
                return datetime;
            }


            public static Boolean ExecSQL(string strSql, string strConnName)
            {
                SetWaitCursor();
                Boolean bl = true;
                string strMess = string.Empty;

                //记时
                DateTime startdt = System.DateTime.Now;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                bl = dbaccess.ExecSQL(strSql, strConnName, ref strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                bl = PubVar.objdb.ExecSQL(strSql, strConnName, ref strMess);
                                if (!bl)
                                {
                                    throw new Exception(strMess);
                                }
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                bl = PubVar.WcfProxy.IExecSQL(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                if (!bl)
                                {
                                    throw new Exception(strMess);
                                }
                                break;
                            }

                    } //switch
                    if (PubVar.SysUpdateSQL)
                    {
                        TimeSpan ts = System.DateTime.Now - startdt;
                        SysTools.WriteTxt(PubVar.LogFile, string.Format("【执行SQL】耗时{0:0.000}秒.连接名称:{1},SQL语句:\r\n{2}\r\n", ts.TotalSeconds, strConnName, strSql));
                    }
                }
                catch (Exception ex)
                {
                    strMess = ex.Message.ToString();
                    SetDefaultCursor();
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【执行SQL错误】连接名称:{0},SQL语句:\r\n{1}\r\n【错误提示】:\r\n{2}\r\n", strConnName, strSql, strMess));
                    MsgTools.ErrorMsg(strMess);
                    return false;
                }
                finally
                {
                    SetDefaultCursor();
                }
                return bl;
            }


            # region GetDataTable

            /// <summary>
            ///  返回(SQL语句、连接名称)生成的DataTable
            /// </summary>
            /// <param name="dat">连接类型</param>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dbname">Table名称</param>
            /// <returns> 返回(SQL语句、连接名称)生成的DataTable</returns>
            public static DataTable GetDataTable(string strSql, string strConnName, string strTable, DataAccessType dat)
            {
                SetWaitCursor();
                DataTable dt = new DataTable();
                string strMess = string.Empty;

                //记时
                DateTime startdt = System.DateTime.Now;
                try
                {
                    switch (dat)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                //   if (dbaccess.CheckUser(PubVar.SysUserCode, PubVar.SysUserCode))
                                {
                                    dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName, strTable,ref  strMess);
                                }
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                //dt = PubVar.objdb.GetDataTable(strSql, strConnName);
                                string strXML = PubVar.objdb.GetDataTableXmlZip(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                if (strXML == string.Empty)
                                {
                                    throw new Exception(strMess);
                                }

                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.XmlToDataTable(strXML);        //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志
                                Remoting.UnLoadRemoting();
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                string strXML = PubVar.WcfProxy.IGetDataTableXmlZip(PubVar.SysArrAuth,  strSql, strConnName, ref strMess);
                                if (strXML == string.Empty)
                                {
                                    throw new Exception(strMess);
                                    // MsgTools.ErrorMsg(strMess);
                                    // return dt;
                                }

                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.XmlToDataTable(strXML);        //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志
                                break;
                            }

                    }//switch
                    if (PubVar.SysUpdateSQL)
                    {
                        TimeSpan ts = System.DateTime.Now - startdt;
                        SysTools.WriteTxt(PubVar.LogFile, string.Format("【读SQL】耗时{0:0.000}秒.连接名称:{1},SQL语句:\r\n{2}\r\n", ts.TotalSeconds, strConnName, strSql));
                    }
                }
                catch (Exception ex)
                {
                    strMess = ex.Message.ToString();
                    SetDefaultCursor();
                    dt.Dispose();
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【读SQL错误】连接名称:{0},SQL语句:\r\n{1}\r\n【错误提示】:\r\n{2}\r\n", strConnName, strSql, strMess));
                    MsgTools.ErrorMsg(strMess);
                    return null;
                }
                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }


            public static DataTable GetDataTable(string strSql, string strConnName, DataAccessType dat)
            {
                return GetDataTable(strSql, strConnName, string.Empty, dat);
            }

            /// <summary>
            ///  (简化)返回(SQL语句、连接名称)生成的DataTable
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dbname">Table名称</param>
            /// <returns> 返回(SQL语句、连接名称)生成的DataTable</returns>
            public static DataTable GetDataTable(string strSql, string strConnName)
            {
                DataTable dt = GetDataTable(strSql, strConnName, PubVar.SysDAT);
                return dt;
            }

            /// <summary>
            /// 返回由SQL语句生成的可写的DataTable
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <returns>返回由SQL语句生成的可写的DataTable</returns>        
            public static DataTable GetDataTable(string strSql)
            {
                return GetDataTable(strSql, PubVar.ConnMain);
            }


            # endregion GetDataTable

            private static string GetColumnSplit(DataColumn col)
            {
                string intWidth = string.Empty;
                string strType = col.DataType.Name;
                if ((strType.ToLower().IndexOf("string") > -1) || (strType.ToLower().IndexOf("date") > -1))
                {
                    intWidth = "'";
                }
                return intWidth;
            }

            private static string GetSQLFilter(string strSql, DataTable changedt, string strdbname)
            {
                string strFilter = string.Empty;
                string strPK = string.Empty;
                DataColumn[] cols = changedt.PrimaryKey;

                string[] strwhere = new string[changedt.Rows.Count];
                if (cols.Length == 0) { return strFilter; }
                int WherePos = 0;

                DataTable dtModified = changedt.GetChanges(DataRowState.Modified);

                if (dtModified != null)
                {
                    for (int irows = 0; irows < dtModified.Rows.Count; irows++)
                    {
                        for (int i = 0; i < cols.Length; i++)
                        {
                            string strtmp = dtModified.Rows[irows][cols[i].ColumnName].ToString();
                            string strfh = GetColumnSplit(dtModified.Columns[cols[i].ColumnName]); //根据类型得到分隔符
                            strtmp = strfh + strtmp + strfh;

                            strwhere[WherePos] = strwhere[WherePos] == null ? strtmp : strwhere[i] + "," + strtmp;

                            WherePos = WherePos + 1;
                        }
                    }
                }

                DataTable dtDeleted = changedt.GetChanges(DataRowState.Deleted);

                if (dtDeleted != null)
                {
                    for (int irows = 0; irows < dtDeleted.Rows.Count; irows++)
                    {
                        for (int i = 0; i < cols.Length; i++)
                        {
                            string strtmp = dtDeleted.Rows[irows][cols[i].ColumnName, DataRowVersion.Original].ToString();
                            string strfh = GetColumnSplit(dtDeleted.Columns[cols[i].ColumnName]); //根据类型得到分隔符
                            strtmp = strfh + strtmp + strfh;

                            strwhere[WherePos] = strwhere[WherePos] == null ? strtmp : strwhere[i] + "," + strtmp;

                            WherePos = WherePos + 1;
                        }
                    }
                }

                for (int i = 0; i < cols.Length; i++)
                {
                    if (strwhere[i] != null)
                    {
                        string strtmp = strdbname + "." + cols[i].ColumnName + " in (" + strwhere[i] + ")";

                        strFilter = strFilter == string.Empty ? strtmp : strFilter + " and " + strtmp;
                    }
                }

                if (strSql.ToLower().IndexOf("where") > -1)
                {
                    strFilter = strFilter == string.Empty ? string.Empty : " and (" + strFilter + ")";
                }
                else
                {
                    strFilter = strFilter == string.Empty ? string.Empty : " Where (" + strFilter + ")";
                }
                return strFilter;
            }


            # region SaveDataTable

            /// <summary>
            /// 保存DataTable(完整函数)
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dt">需要保存的DataTable</param>
            /// <param name="strConnName">连接名称</param>
            /// <param name="dat">DataAccessType类型</param> 
            /// <param name="strTableName">表名</param>
            /// <param name="strTableFields">字段名</param>
            /// <returns></returns>
            public static Boolean SaveDataTable(string strSql, DataTable dt, string strConnName, string strTableName, DataAccessType dat, string strTableFields)
            {

                Boolean bl = false;
                //记时
                DateTime startdt = System.DateTime.Now;
                SetWaitCursor();
                string strMess = string.Empty;

                if ((dt == null) || (dt.GetChanges() == null))
                {
                    // "无数据发生修改,不需保存."
                    return true;
                }

                //新增数
                int AddedNum = 0;

                //修改数
                int ModifiedNum = 0;

                //删除数
                int DeletedNum = 0;

                if (PubVar.SysUpdateSQL)
                {
                    //新增数
                    DataTable dtAdded = dt.GetChanges(DataRowState.Added);
                    if (dtAdded != null)
                    {
                        AddedNum = dtAdded.Rows.Count;
                        dtAdded.Dispose();
                    }

                    //修改数
                    DataTable dtModified = dt.GetChanges(DataRowState.Modified);
                    if (dtModified != null)
                    {
                        ModifiedNum = dtModified.Rows.Count;
                        dtModified.Dispose();
                    }

                    //删除数
                    DataTable dtDeleted = dt.GetChanges(DataRowState.Deleted);
                    if (dtDeleted != null)
                    {
                        DeletedNum = dtDeleted.Rows.Count;
                        dtDeleted.Dispose();
                    }
                }

                try
                {
                    switch (dat)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                bl = dbaccess.UpdateDb(PubVar.SysArrAuth, strSql, dt.GetChanges(), strConnName, strTableName, strTableFields);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                string strxml = ZipString.CompressString(XmlDB.SerializeDataTableToXml(dt.GetChanges())); //压缩字符串
                                bl = PubVar.objdb.SaveDT(PubVar.SysArrAuth, strSql, strxml, strConnName, strTableName, strTableFields, ref strMess);

                                Remoting.UnLoadRemoting();
                                if (!bl)
                                {
                                    throw new Exception(strMess);
                                }
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                string strxml = ZipString.CompressString(XmlDB.SerializeDataTableToXml(dt.GetChanges())); //压缩字符串

                                bl = PubVar.WcfProxy.ISaveDT(PubVar.SysArrAuth, strSql, strxml, strConnName, strTableName, strTableFields, ref strMess);
                                if (!bl)
                                {
                                    throw new Exception(strMess);
                                }
                                break;
                            }
                    }//switch
                    if (PubVar.SysUpdateSQL)
                    {
                        TimeSpan ts = System.DateTime.Now - startdt;
                        SysTools.WriteTxt(PubVar.LogFile, string.Format("【提交SQL】新增:{0},修改:{1},删除:{2}.共耗时{3:0.000}秒.连接名称:{4},SQL语句:\r\n{5}\r\n", AddedNum, ModifiedNum, DeletedNum, ts.TotalSeconds, strConnName, strSql));
                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【提交SQL错误】连接名称:{0},SQL语句:\r\n{1}\r\n【错误提示】:\r\n{2}\r\n", strConnName, strSql, strMess));
                    MsgTools.ErrorMsg(ex.Message.ToString());

                    return false;
                }
                finally
                {
                    SetDefaultCursor();
                }
                return bl;
            }




            /// <summary>
            /// 保存DataTable(缺省字段名TableFields)
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dt">需要保存的DataTable</param>
            /// <param name="strConnName">连接名称</param>
            /// <param name="strTableName">TableName名称</param>
            /// <param name="dat">DataAccessType类型</param> 
            /// <returns></returns>
            public static Boolean SaveDataTable(string strSql, DataTable dt, string strConnName, string strTableName, DataAccessType dat)
            {
                string strTableFields = DBClass.GetFieldsName(strSql, strTableName, dt);
                return SaveDataTable(strSql, dt, strConnName, strTableName, dat, strTableFields);
            }


            /// <summary>
            /// 保存DataTable(缺省类型DataAccessType)
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dt">需要保存的DataTable</param>
            /// <param name="strConnName">连接名称</param>
            /// <param name="strTableName">TableName名称</param>
            /// <returns></returns>
            public static Boolean SaveDataTable(string strSql, DataTable dt, string strConnName, string strTableName)
            {
                return SaveDataTable(strSql, dt, strConnName, strTableName, PubVar.SysDAT);
            }

            /// <summary>
            /// 保存DataTable(缺省表名TableName)
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dt">需要保存的DataTable</param>
            /// <param name="strConnName">连接名称</param>
            /// <param name="dat">DataAccessType类型</param> 
            /// <returns></returns>
            public static Boolean SaveDataTable(string strSql, DataTable dt, string strConnName)
            {
                string strTableName = DBClass.GetDBName(strSql);
                Boolean bl = SaveDataTable(strSql, dt, strConnName, strTableName);
                return bl;
            }


            /// <summary>
            ///  保存DataTable(缺省DataAccessType)
            /// </summary>
            /// <param name="strSql">SQL语句</param>
            /// <param name="dt">需要保存的DataTable</param>
            /// <returns></returns>
            public static Boolean SaveDataTable(string strSql, DataTable dt)
            {
                Boolean bl = false;
                bl = SaveDataTable(strSql, dt, PubVar.ConnMain);
                return bl;
            }

            # endregion SaveDataTable

            /// <summary>
            /// 获取连接字符串
            /// </summary>
            /// <param name="_conn">连接名称</param>
            /// <returns>获取连接名称的连接字符串</returns>
            public static string GetConnstring(string _conn)
            {
                string strconn = string.Empty;
                string strMess = string.Empty;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {

                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                string ConnectionProviderName = string.Empty;
                                strconn = PubVar.objdb.GetConnectionStringByName(_conn, ref ConnectionProviderName);
                                Remoting.UnLoadRemoting();
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                WCF.LoadWCF();
                                strconn = PubVar.WcfProxy.IGetConnstring(PubVar.SysArrAuth, _conn, ref strMess);
                                break;
                            }

                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    throw ex;
                }
                return strconn;
            }

            /// <summary>
            /// 获取连接字符串
            /// </summary>
            /// <returns>获取默认的连接字符串</returns>
            public static string GetConnstring()
            {
                return GetConnstring(PubVar.ConnMain);
            }

            private static void FindIDENTITY(DataTable dt)
            {
                if (dt == null) { return; }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].AutoIncrement)
                    {
                        SetIDENTITY(dt, dt.Columns[i].ToString());
                    }
                }
            }

            private static void SetIDENTITY(DataTable dt, string idName)
            {
                if (dt == null) { return; }
                dt.Columns[idName].AutoIncrement = true;
                dt.Columns[idName].AutoIncrementStep = -1;
                dt.Columns[idName].AutoIncrementSeed = -1;

                //dt.Columns[idName].AutoIncrementSeed = -(dt.Rows.Count + 1);

            }


            public static DataTable GetDataTableXML(string strSql, string strConnName)
            {
                SetWaitCursor();
                DataTable dt = new DataTable();
                string strMess = string.Empty;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                //dt = PubVar.objdb.GetDataTable(strSql, strConnName);
                                string strXML = PubVar.objdb.GetDataTableXml(PubVar.SysArrAuth, strSql, strConnName,ref  strMess);

                                dt = XmlDB.XmlToDataTable(strXML);        //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志
                                Remoting.UnLoadRemoting();
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                string strXML = PubVar.WcfProxy.IGetDataTableXml(PubVar.SysArrAuth, strSql, strConnName, ref strMess);

                                dt = XmlDB.XmlToDataTable(strXML);      //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志
                                break;
                            }
                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    dt.Dispose();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    return null;
                }

                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }


            public static DataTable GetDataTableXMLZip(string strSql, string strConnName)
            {
                SetWaitCursor();
                DataTable dt = new DataTable();
                string strMess = string.Empty;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                //dt = PubVar.objdb.GetDataTable(strSql, strConnName);

                                string strXML = PubVar.objdb.GetDataTableXmlZip(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                if (strXML == string.Empty)
                                {
                                    MsgTools.ErrorMsg(strMess);
                                    return dt;
                                }
                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.XmlToDataTable(strXML);        //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志

                                //  PubVar.objsysUser.SendFax(MyPub.SysClass.GetIPAddress(), strSql);
                                Remoting.UnLoadRemoting();
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                //     WCF.LoadWCF();
                                string strXML = PubVar.WcfProxy.IGetDataTableXmlZip(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                if (strXML == string.Empty)
                                {
                                    MsgTools.ErrorMsg(strMess);
                                    return dt;
                                }
                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.XmlToDataTable(strXML);        //还原为DataTable
                                dt.AcceptChanges();                         //取消更改标志

                                //  WCF.WCFClose();
                                break;
                            }
                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    dt.Dispose();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    return null;
                }
                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }

            public static DataTable GetDataTableSerialize(string strSql, string strConnName)
            {
                SetWaitCursor();
                string strMess = string.Empty;
                DataTable dt = new DataTable();
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                string strXML = PubVar.objdb.GetDataTableXmlSerialize(PubVar.SysArrAuth, strSql, strConnName, ref strMess);

                                dt = XmlDB.SerializeXmlToDataTable(strXML);    //还原

                                Remoting.UnLoadRemoting();
                                break;
                            }

                        case DataAccessType.WCF:
                            {
                                string strXML = PubVar.WcfProxy.IGetDataTableXmlSerialize(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                dt = XmlDB.SerializeXmlToDataTable(strXML);    //还原
                                break;
                            }
                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    dt.Dispose();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    return null;
                }
                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }

            public static DataTable GetDataTableSeriesZip(string strSql, string strConnName)
            {
                SetWaitCursor();
                DataTable dt = new DataTable();
                string strMess = string.Empty;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName,ref strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                string strXML = PubVar.objdb.GetDataTableXmlSerializeZip(PubVar.SysArrAuth, strSql, strConnName,ref strMess);
                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.SerializeXmlToDataTable(strXML);    //还原
                                Remoting.UnLoadRemoting();
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                //      RemotingDB.LoadRemoting();
                                string strXML = PubVar.WcfProxy.IGetDataTableXmlSerializeZip(PubVar.SysArrAuth,  strSql, strConnName,ref strMess);
                                strXML = ZipString.DecompressString(strXML); //解压
                                dt = XmlDB.SerializeXmlToDataTable(strXML);    //还原
                                //    RemotingDB.UnLoadRemoting();
                                break;
                            }
                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    dt.Dispose();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    return null;
                }
                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }


            public static DataTable GetDataTableOld(string strSql, string strConnName)
            {
                SetWaitCursor();
                DataTable dt = new DataTable();
                string strMess = string.Empty;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                dt = dbaccess.GetDataTable(PubVar.SysArrAuth, strSql, strConnName,ref  strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                Remoting.LoadRemoting();
                                dt = PubVar.objdb.GetDataTable(PubVar.SysArrAuth, strSql, strConnName,ref  strMess);
                                Remoting.UnLoadRemoting();
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                dt = PubVar.WcfProxy.IGetDataTable(PubVar.SysArrAuth, strSql, strConnName, ref strMess);
                                break;
                            }

                    }
                }
                catch (Exception ex)
                {
                    SetDefaultCursor();
                    dt.Dispose();
                    MsgTools.ErrorMsg(ex.Message.ToString());
                    return null;
                }
                finally
                {
                    SetDefaultCursor();
                }
                FindIDENTITY(dt);
                return dt;
            }

            /// <summary>
            /// (简化)获取服务器参数
            /// </summary>
            /// <param name="strcInfKey">参数Key</param>
            /// <returns></returns>
            public static string GetInf(string strcInfKey)
            {
                string strinf = GetInf(strcInfKey, PubVar.ConnMain, PubVar.SysDAT);
                return strinf;
            }

            /// <summary>
            /// 获取服务器参数
            /// </summary>
            /// <param name="strcInfKey">参数Key</param>
            /// <param name="strConn">连接名称</param>
            /// <param name="dat">DataAccessType</param>
            /// <returns></returns>
            public static string GetInf(string strcInfKey, string strConn, DataAccessType dat)
            {
                string strcInfValue = string.Empty;
                string strSql = "select * from AccInf Where cInfKey = '" + strcInfKey + "'";
                DataTable dt = GetDataTable(strSql, strConn, dat);
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        strcInfValue = dt.Rows[0]["cInfValue"].ToString();
                    }
                }
                return strcInfValue;
            }



            /// <summary>
            /// 保存服务器AccInf参数
            /// </summary>
            /// <param name="strcInfKey">参数Key</param>
            /// <param name="strcInfValue">值</param>
            /// <param name="strConn">连接名称</param>
            /// <param name="dat">DataAccessType</param>
            /// <returns></returns>
            public static Boolean SaveInf(string strcInfKey, string strcInfValue, string strConn, DataAccessType dat)
            {
                Boolean bl = false;
                string strSql = "select * from AccInf Where cInfKey = '" + strcInfKey + "'";
                DataTable dt = GetDataTable(strSql, strConn, dat);
                if (dt != null)
                {
                    if (dt.Rows.Count == 0)
                    {
                        DataRow newRow;
                        newRow = dt.NewRow();
                        newRow["cInfKey"] = strcInfKey;
                        dt.Rows.Add(newRow);
                    }
                    dt.Rows[0]["cInfValue"] = strcInfValue;

                    bl = SaveDataTable(strSql, dt, strConn, "AccInf", dat);
                }
                return bl;
            }

            /// <summary>
            /// (简化)保存服务器AccInf参数
            /// </summary>
            /// <param name="strcInfKey">参数Key</param>
            /// <param name="strcInfValue">值</param>
            /// <returns></returns>
            public static Boolean SaveInf(string strcInfKey, string strcInfValue, string strConn)
            {
                Boolean bl = SaveInf(strcInfKey, strcInfValue, strConn, PubVar.SysDAT);
                return bl;
            }


            /// <summary>
            /// (简化)保存服务器AccInf参数
            /// </summary>
            /// <param name="strcInfKey">参数Key</param>
            /// <param name="strcInfValue">值</param>
            /// <returns></returns>
            public static Boolean SaveInf(string strcInfKey, string strcInfValue)
            {
                Boolean bl = SaveInf(strcInfKey, strcInfValue, PubVar.ConnMain, PubVar.SysDAT);
                return bl;
            }

        }



        public static class ADHelper
        {
            /// <summary>
            /// 身份验证(WINDOWS、数据库)
            /// </summary>
            /// <param name="domain">AD域名</param>
            /// <param name="userName">用户名</param>
            /// <param name="password">密码</param>
            /// <param name="strMess">返回错误的信息</param>
            /// <returns>返回AD是否成功校验</returns>
            public static Boolean AuthUser(string ArrAuth, ref string strMess)
            {
                SetWaitCursor();
                Boolean bl = false;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                bl = dbaccess.IAuthUser(ArrAuth, ref  strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                bl = PubVar.objdb.IAuthUser(ArrAuth, ref  strMess);
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                bl = PubVar.WcfProxy.IAuthUser(ArrAuth, ref  strMess);
                                break;
                            }

                    }
                }
                catch 
                {
                    SetDefaultCursor();
                    MsgTools.ErrorMsg(strMess);
                }
                finally
                {
                    SetDefaultCursor();
                }

                return bl;
            }

            //public static Boolean AuthUser(string userName, string password, ref string strMess)
            //{
            //    return AuthUser(PubVar.Domain, userName, password, ref  strMess);
            //}

            public static Boolean ChangeUserPasswd(string ArrAuth, ref string strMess)
            {
                SetWaitCursor();
                Boolean bl = false;
                try
                {
                    switch (PubVar.SysDAT)
                    {
                        case DataAccessType.CS:
                            {
                                DBAccess dbaccess = new DBAccess();
                                bl = dbaccess.IChangeUserPasswd(ArrAuth, ref  strMess);
                                break;
                            }
                        case DataAccessType.REMOTING:
                            {
                                bl = PubVar.objdb.IChangeUserPasswd(ArrAuth, ref  strMess);
                                break;
                            }
                        case DataAccessType.WCF:
                            {
                                bl = PubVar.WcfProxy.IChangeUserPasswd(ArrAuth, ref  strMess);
                                break;
                            }

                    }
                }
                catch
                {
                    SetDefaultCursor();
                    MsgTools.ErrorMsg(strMess);
                }
                finally
                {
                    SetDefaultCursor();
                }

                return bl;

            }

            //默认域名检查ad
            //public static Boolean ChangeUserPasswd(string ArrAuth, ref string strMess)
            //{
            //    return ChangeUserPasswd(PubVar.Domain, userName, oldPwd, newPwd, ref  strMess);
            //}
        }
    }
}